package com.mairuide.robot.question;

import java.util.LinkedList;
import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;

@Service
@Transactional(readOnly = true)
public class MQuestionService  extends BaseServiceProxy<MQuestion> {
	
	@Resource
	private MQuestionRepository mquestionRepository;
	
	@PersistenceContext
	private EntityManager entityManager;
	
	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}
	
	@Override
	protected BaseRepository<MQuestion> getBaseRepository() {
		return mquestionRepository;
	}
	
	/**
	 * 通过关键字查询问题列表
	 * @param keyword
	 * @param page
	 * @param sortName
	 * @param sortOrder
	 * @return
	 */
	public Page<QueryMQuestion> findQuestion4KeyWord(String keyword, Pageable page,String sortName,String sortOrder) {
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		StringBuffer fromWhereSql = new StringBuffer();
		selectSql.append(" select * from (select listagg(tp.participles, ',') within group(order by tp.qp_id) as sbparticiples,\r\n" + 
				"       tp.qp_id as qid,\r\n" + 
				"       max(tq.question) as question\r\n" + 
				"  from tb_m_participles tp, tb_m_question tq\r\n" + 
				" where tp.qp_id = tq.q_id ");
		if(StringUtils.isNotEmpty(keyword)) {
			selectSql.append(" and (tp.participles = '"+keyword+"' or tp.participles like '%"+keyword+"%' ");
			selectSql.append(" or tq.question = '"+keyword+"' or tq.question like '%"+keyword+"%') ");
		}
		selectSql.append(" group by tp.qp_id) a ");
		String orderBySql = " order by a.qid desc";
		//封装返回数据
		return super.findPageBySql4GroupBy(QueryMQuestion.class,selectSql.toString(), fromWhereSql.toString(), orderBySql, params, page);

	}
	
	/**
	 * 通过分词查询问题内容
	 * @param qs
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<MQuestion> findQuestionByParticiples(String qs){
		StringBuffer sbf = new StringBuffer();
		sbf.append("select * from tb_m_question q where q.q_id in(select t.qp_id ");
		sbf.append("from tb_m_participles t where ");
		if(StringUtils.isNotEmpty(qs)) {
			sbf.append("t.participles in (");
			String[] arrqs = qs.split(",");
			for(String q : arrqs) {
				sbf.append(""+q+",");
			}
			sbf.delete(sbf.length() -1, sbf.length());
			sbf.append(") ");
		}
		sbf.append("and t.priority =(select min(t2.priority) from tb_m_participles t2 where ");
		if(StringUtils.isNotEmpty(qs)) {
			sbf.append("t2.participles in (");
			String[] arrqs = qs.split(",");
			for(String q : arrqs) {
				sbf.append(""+q+",");
			}
			sbf.delete(sbf.length() -1, sbf.length());
			sbf.append("))) ");
		}
		Query query = getEntityManager().createNativeQuery(sbf.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(MQuestion.class));
		//封装返回数据
		return query.getResultList();
	}
}
